/**
 * 
 */
package com.feib.stms.dao.jdbc;


import java.sql.Types;

import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer;
import org.springframework.stereotype.Repository;

import com.feib.stms.dao.UserLoginControlDao;
import com.feib.stms.dao.jdbc.rowmapper.UserLoginControlRowMapper;
import com.feib.stms.model.User;
import com.feib.stms.model.UserLoginControl;

/**
 * @author Jimmy Liu
 *
 */
@Repository("userLoginControlDao")
public class UserLoginControlJdbcDao extends BaseJdbcDao implements UserLoginControlDao 
{
	private OracleSequenceMaxValueIncrementer sequence;
	
	private SimpleJdbcInsert userLoginControlJdbcInsert;
	
	@Override
	protected void initDao() throws Exception {
		super.initDao();
		this.sequence = new  OracleSequenceMaxValueIncrementer(this.getDataSource(), "USER_LOGIN_CONTROL_PK_SEQ");
		this.userLoginControlJdbcInsert = new  SimpleJdbcInsert(this.getDataSource());
		this.userLoginControlJdbcInsert.withTableName("USER_LOGIN_CONTROL")
			.usingColumns("USER_LOGIN_CONTROL_PK", "USER_PK", "SESSION_ID", "LOGIN_TIME", "LOGIN_LOCALE_IP"
					, "IS_ACTIVED", "ACTIVITY_TIME");
		
	}
	
	public int updateActived(UserLoginControl userLoginControl) 
	{
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		
		sql.append("UPDATE USER_LOGIN_CONTROL SET IS_ACTIVED = :IS_ACTIVED ");
		parameters.addValue("IS_ACTIVED", convertToInteger(userLoginControl.getActived()), Types.NUMERIC);
		if (Boolean.TRUE.equals(userLoginControl.getActived()))
		{
			sql.append(", ACTIVITY_TIME = :ACTIVITY_TIME");
			parameters.addValue("ACTIVITY_TIME",  userLoginControl.getActivityTime(), Types.TIMESTAMP);
		}
		sql.append(" WHERE USER_LOGIN_CONTROL_PK = :USER_LOGIN_CONTROL_PK");
		parameters.addValue("USER_LOGIN_CONTROL_PK",  userLoginControl.getUserLoginControlPk(), Types.NUMERIC);
		
		return this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
	}

	public int insert(UserLoginControl userLoginControl) {
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		
		Long userLoginControlPk = new Long(sequence.nextLongValue());
		parameters.addValue("USER_LOGIN_CONTROL_PK",  userLoginControlPk, Types.NUMERIC);
		parameters.addValue("USER_PK",   userLoginControl.getUser().getUserPk(), Types.NUMERIC);
		parameters.addValue("SESSION_ID",  userLoginControl.getSessionId(), Types.VARCHAR);
		parameters.addValue("LOGIN_TIME",  userLoginControl.getLoginTime(), Types.TIMESTAMP);
		parameters.addValue("LOGIN_LOCALE_IP",  userLoginControl.getLoginLocaleIP(), Types.VARCHAR);
		
		parameters.addValue("IS_ACTIVED",  convertToInteger(userLoginControl.getActived()), Types.NUMERIC);
		parameters.addValue("ACTIVITY_TIME",  userLoginControl.getActivityTime(), Types.TIMESTAMP);
		
		int updateCounts = userLoginControlJdbcInsert.execute(parameters);		

		if (updateCounts > 0)
			userLoginControl.setUserLoginControlPk(userLoginControlPk);
		
		return updateCounts;
	}

	public int update(UserLoginControl userLoginControl) 
	{
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		
		sql.append("UPDATE USER_LOGIN_CONTROL SET USER_PK = :USER_PK");
		sql.append(", SESSION_ID = :SESSION_ID");
		sql.append(", LOGIN_TIME = :LOGIN_TIME");
		sql.append(", LOGIN_LOCALE_IP = :LOGIN_LOCALE_IP");
		
		sql.append(", IS_ACTIVED = :IS_ACTIVED");
		sql.append(", ACTIVITY_TIME = :ACTIVITY_TIME");
		sql.append(" WHERE USER_LOGIN_CONTROL_PK = :USER_LOGIN_CONTROL_PK");

		parameters.addValue("USER_PK",  userLoginControl.getUser().getUserPk(), Types.NUMERIC);
		parameters.addValue("SESSION_ID",  userLoginControl.getSessionId(), Types.VARCHAR);
		parameters.addValue("LOGIN_TIME",  userLoginControl.getLoginTime(), Types.TIMESTAMP);		
		parameters.addValue("LOGIN_LOCALE_IP",  userLoginControl.getLoginLocaleIP(), Types.VARCHAR);
		
		parameters.addValue("IS_ACTIVED",  convertToInteger(userLoginControl.getActived()), Types.INTEGER);
		parameters.addValue("ACTIVITY_TIME",  userLoginControl.getActivityTime(), Types.TIMESTAMP);
		
		parameters.addValue("USER_LOGIN_CONTROL_PK",  userLoginControl.getUserLoginControlPk(), Types.NUMERIC);
		
		return this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
	}

	public UserLoginControl getByUerIdGroupNo(User user) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		
		sql.append("SELECT C.*, G.GROUP_PK");
		sql.append(" , U.USER_ID, G.GROUP_NO");
		sql.append(" FROM USER_LOGIN_CONTROL C, APP_USER U, APP_GROUP G ");
		sql.append(" WHERE C.USER_PK = U.USER_PK  ");
		sql.append(" AND G.GROUP_PK = U.GROUP_PK ");
		sql.append(" AND U.USER_ID = :USER_ID AND G.GROUP_NO = :GROUP_NO");
		parameters.addValue("USER_ID",  user.getUserId(), Types.VARCHAR);
		parameters.addValue("GROUP_NO", null == user.getGroup() ? null: user.getGroup().getGroupNo(), Types.VARCHAR);
		
		return DataAccessUtils.singleResult(this.getSimpleJdbcTemplate().query(sql.toString(), new UserLoginControlRowMapper(), parameters));
	}

}

